package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.UserDao;
import com.cn.wanxi.model.PageModel;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.GetSqlSession;
import com.cn.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserImpl implements UserDao {
    @Override
    public int add(UserModel userModel) {
//        String sql = "insert into user" +
//                "(username,password,sex,phone,hobby,email,create_time,birthday)" +
//                "values ('" + userModel.getUsername() + "'," +
//                "'" + userModel.getPassword() + "'," +
//                "'" + userModel.getSex() + "'," +
//                "'" + userModel.getPhone() + "'," +
//                "'" + userModel.getHobby() + "'," +
//                "'" + userModel.getEmail() + "'," +
//                "now()," +
//                "'" + userModel.getBirthday() + "')";
//        return JDBC.update(sql);
        return GetSqlSession.getSqlSession().insert("com.cn.wanxi.dao.UserDao.add", userModel);
    }

    @Override
    public boolean findByUsername(String username) {
//        String sql = "select * from user where username='" + username + "'";
//        ResultSet resultSet = JDBC.query(sql);//和list差不多，用法
//        boolean ishave = false;
//        try {
//            while (resultSet.next()) {
//                ishave = true;
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
        return GetSqlSession.getSqlSession().selectOne("com.cn.wanxi.dao.UserDao.findByUsername", username) != null;
    }

    @Override
    public UserModel findByUsernameAndPassword(UserModel userModel) {
//        String sql = "select * from user where username='" + userModel.getUsername() + "' and password= '" + userModel.getPassword() + "'";
//        ResultSet resultSet = JDBC.query(sql);
//        UserModel model = null;
//        try {
//            while (resultSet.next()) {
//                model = new UserModel();
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
        return GetSqlSession.getSqlSession().selectOne("com.cn.wanxi.dao.UserDao.findByUsernameAndPassword", userModel);
    }

    @Override
    public List<UserModel> findAll(UserModel userModel) {
//        StringBuilder sql = new StringBuilder();
//        sql.append("select * from user  where true");
//        if (!"".equals(userModel.getUsername())) {
//            sql.append(" and username like '%").append(userModel.getUsername()).append("%'");
//        }
//        if (!"".equals(userModel.getPhone())) {
//            sql.append(" and phone like '%").append(userModel.getPhone()).append("%'");
//        }
//        if (!"".equals(userModel.getEmail())) {
//            sql.append(" and email like '%").append(userModel.getEmail()).append("%'");
//        }
//        if (!"".equals(userModel.getHobby())) {
//            String hobby = userModel.getHobby();
//            String[] hobbys = hobby.split(",");
//            sql.append(" and (");
//            for (int i = 0; i < hobbys.length; i++) {
//                if (i == 0) {
//                    sql.append("  find_in_set('").append(hobbys[i]).append("',hobby)");
//                } else {
//                    sql.append(" or find_in_set('").append(hobbys[i]).append("',hobby)");
//                }
//            }
//            sql.append(")");
//        }
////        if (!"全部".equals(userModel.getSex())) {
////            sql.append(" and sex ='").append(userModel.getSex()).append("'");
////        }
//
////        生日
//        if (!"".equals(userModel.getStartBirthday()) && "".equals(userModel.getEndBirthday())) {
//            sql.append(" and birthday >='").append(userModel.getStartBirthday()).append("'");
//        }
//        if ("".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
//            sql.append(" and birthday <='").append(userModel.getEndBirthday()).append("'");
//        }
//        if (!"".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
//            sql.append(" and birthday between'").append(userModel.getStartBirthday()).append("' and '").append(userModel.getEndBirthday()).append("'");
//        }
//        if (userModel.getEnable() != -1) {
//            if (userModel.getEnable() == 1) {
//                sql.append(" and enable=1");
//            } else {
//                sql.append(" and (enable is null or enable =0)");
//            }
//        }
//        sql.append(" order by id desc");
////        limit  (curPage-1)*pageSize,pageSize//分页公司
//        sql.append(" limit ").append((userModel.getPageModel().getCurPage() + -1) * userModel.getPageModel().getPageSize()).append(",").append(userModel.getPageModel().getPageSize());
//        ResultSet resultSet = JDBC.query(sql.toString());
        return GetSqlSession.getSqlSession().selectList("com.cn.wanxi.dao.UserDao.findAll",userModel);
    }

    @Override
    public int del(Integer id) {
//        String sql = "delete from user where id=" + id;
        return GetSqlSession.getSqlSession().delete("com.cn.wanxi.dao.UserDao.del", id);
    }

    @Override
    public UserModel findById(Integer id) {
//        String sql = "select * from user where id=" + id;
//        ResultSet resultSet = JDBC.query(sql);
//        assert resultSet != null;
//        List<UserModel> list = getData(resultSet);
//        return list.size() > 0 ? list.get(0) : null;
        List<UserModel> list=GetSqlSession.getSqlSession().selectList("com.cn.wanxi.dao.UserDao.findById", id);
        return list.size() > 0 ? list.get(0) : null;
    }

    @Override
    public int edit(UserModel userModel) {
        String sql = "update user set " +
                "username='" + userModel.getUsername() + "'," +
                "phone='" + userModel.getPhone() + "'," +
                "hobby='" + userModel.getHobby() + "'," +
                "sex='" + userModel.getSex() + "'," +
                "birthday='" + userModel.getBirthday() + "'," +
                "email='" + userModel.getEmail() + "'" +
                " where id=" + userModel.getId();
        return JDBC.update(sql);
    }

    @Override
    public int getCount(UserModel userModel) {
        StringBuilder sql = new StringBuilder();
        sql.append("select count(*) count from user  where true");
        if (!"".equals(userModel.getUsername())) {
            sql.append(" and username like '%").append(userModel.getUsername()).append("%'");
        }
        if (!"".equals(userModel.getPhone())) {
            sql.append(" and phone like '%").append(userModel.getPhone()).append("%'");
        }
        if (!"".equals(userModel.getEmail())) {
            sql.append(" and email like '%").append(userModel.getEmail()).append("%'");
        }
        if (!"".equals(userModel.getHobby())) {
            String hobby = userModel.getHobby();
            String[] hobbys = hobby.split(",");
            sql.append(" and (");
            for (int i = 0; i < hobbys.length; i++) {
                if (i == 0) {
                    sql.append("  find_in_set('").append(hobbys[i]).append("',hobby)");
                } else {
                    sql.append(" or find_in_set('").append(hobbys[i]).append("',hobby)");
                }
            }
            sql.append(")");
        }
//        if (!"全部".equals(userModel.getSex())) {
//            sql.append(" and sex ='").append(userModel.getSex()).append("'");
//        }

//        生日
        if (!"".equals(userModel.getStartBirthday()) && "".equals(userModel.getEndBirthday())) {
            sql.append(" and birthday >='").append(userModel.getStartBirthday()).append("'");
        }
        if ("".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql.append(" and birthday <='").append(userModel.getEndBirthday()).append("'");
        }
        if (!"".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql.append(" and birthday between'").append(userModel.getStartBirthday()).append("' and '").append(userModel.getEndBirthday()).append("'");
        }
        if (userModel.getEnable() != -1) {
            if (userModel.getEnable() == 1) {
                sql.append(" and enable=1");
            } else {
                sql.append(" and (enable is null or enable =0)");
            }
        }
        sql.append(" order by id desc");
        ResultSet resultSet = JDBC.query(sql.toString());
        int count = 0;
        try {
            while (resultSet.next()) {
                count = resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    private List<UserModel> getData(ResultSet resultSet) {
        List<UserModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                UserModel model = new UserModel();
                model.setId(resultSet.getInt("id"));
                model.setUsername(resultSet.getString("username"));
                model.setPhone(resultSet.getString("phone"));
                model.setEmail(resultSet.getString("email"));
                model.setHobby(resultSet.getString("hobby"));
                model.setSex(resultSet.getString("sex"));
                model.setBirthday(resultSet.getString("birthday"));
                model.setEnable(resultSet.getInt("enable"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}
